﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using Auto.Data.DBUtility;
using Auto.Data.BusMan.IDAL;
using Auto.Components;
using Model = Auto.Data.BusMan.Model;
namespace Auto.Data.BusMan.SQLServerDAL
{
    /// <summary>
    ///  Data Access Class BusMaintenance。
    /// </summary>
    public class BusMaintenance : IBusMaintenance
    {
        #region --New--
        /// <sumary> 
        /// Constructed Function 
        /// </sumary> 
        public BusMaintenance()
        {
        }
        /// <summary>
        /// Get Model
        /// </summary>
        /// <param name="objKey">key of Table</param>
        public Model.BusMaintenance GetModel(params object[] objKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT TOP 1 RecID, DocID, BusID, BusIdentificationNO, MaintenanceTypeID, MaintenanceTypeName, MaintenanceDate, MaintenanceManufacturer,MaintenanceManufacturerDesc,MaintenanceManufacturerOther, MaintenancePrincipal, MaintenanceMajorComponent, TotalCost, MaintenanceOutList, RecDivID, RecAcct, RecDate, Void, VoidAcct, VoidDate, UserAcct, LastUpdateDate");
            strSql.Append(" FROM Bus_BusMaintenance");
            strSql.Append(" WHERE DocID=@DocID");
            SqlParameter[] parameters = SetParametersValue(objKey);
            Model.BusMaintenance model;
            DataRow dr = DbHelper.GetRecord(strSql.ToString(), parameters);
            if (dr != null)
            {
                model = new Model.BusMaintenance(dr);
                return model;
            }
            else
            {
                return null;
            }
        }
        #endregion --New--
        #region --Operate--
        /// <summary>
        /// Is Exists Or Not
        /// </summary>
        public bool Exists(params object[] objKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(1) FROM BusMaintenance  ");
            strSql.Append(" FROM Bus_BusMaintenance ");
            strSql.Append(" WHERE DocID=@DocID");
            SqlParameter[] parameters = SetParametersValue(objKey);
            return DbHelper.Exists(strSql.ToString(), parameters);
        }
        #endregion --Operate--
        #region --SQL--
        /// <summary>
        /// Add One Record
        /// </summary>
        public void Add(Model.BusMaintenance model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO Bus_BusMaintenance(");
            strSql.Append("DocID,BusID,BusIdentificationNO,MaintenanceTypeID,MaintenanceTypeName,MaintenanceDate,MaintenanceManufacturer,MaintenanceManufacturerDesc,MaintenanceManufacturerOther,MaintenancePrincipal,MaintenanceMajorComponent,TotalCost,MaintenanceOutList,RecDivID,RecAcct,RecDate,UserAcct,LastUpdateDate)");//Void,VoidAcct,VoidDate,
            strSql.Append(" VALUES (");
            strSql.Append("@DocID,@BusID,@BusIdentificationNO,@MaintenanceTypeID,@MaintenanceTypeName,@MaintenanceDate,@MaintenanceManufacturer,@MaintenanceManufacturerDesc,@MaintenanceManufacturerOther,@MaintenancePrincipal,@MaintenanceMajorComponent,@TotalCost,@MaintenanceOutList,@RecDivID,@RecAcct,@RecDate,@UserAcct,@LastUpdateDate)");//@Void,@VoidAcct,@VoidDate,
            strSql.Append(";UPDATE Bus_BusMaintenance SET DocID=RecID WHERE RecID=@@IDENTITY");
            SqlParameter[] parameters = SetParametersValue(model);
            try
            {
                object obj = DbHelper.ExecuteNonQuery(strSql.ToString(), parameters);

                if (obj == null)
                {
                    throw new Exception("Error When Insert Data Into BusMaintenance.");
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Update One Record
        /// </summary>
        public void Update(Model.BusMaintenance model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE Bus_BusMaintenance SET ");
            strSql.Append("BusID=@BusID,");
            strSql.Append("BusIdentificationNO=@BusIdentificationNO,");
            strSql.Append("MaintenanceTypeID=@MaintenanceTypeID,");
            strSql.Append("MaintenanceTypeName=@MaintenanceTypeName,");
            strSql.Append("MaintenanceDate=@MaintenanceDate,");
            strSql.Append("MaintenanceManufacturer=@MaintenanceManufacturer,");
            strSql.Append("MaintenanceManufacturerDesc=@MaintenanceManufacturerDesc,");
            strSql.Append("MaintenanceManufacturerOther=@MaintenanceManufacturerOther,");
            strSql.Append("MaintenancePrincipal=@MaintenancePrincipal,");
            strSql.Append("MaintenanceMajorComponent=@MaintenanceMajorComponent,");
            strSql.Append("TotalCost=@TotalCost,");
            strSql.Append("MaintenanceOutList=@MaintenanceOutList,");
            strSql.Append("RecDivID=@RecDivID,");
            strSql.Append("RecAcct=@RecAcct,");
            strSql.Append("RecDate=@RecDate,");
            //strSql.Append("Void=@Void,");
            //strSql.Append("VoidAcct=@VoidAcct,");
            //strSql.Append("VoidDate=@VoidDate,");
            strSql.Append("UserAcct=@UserAcct,");
            strSql.Append("LastUpdateDate=@LastUpdateDate");
            strSql.Append(" WHERE DocID=@DocID ");
            SqlParameter[] parameters = SetParametersValue(model);
            DbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
        }
        /// <summary>
        /// Delete One Record
        /// </summary>
        public void Delete(params object[] objKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE Bus_BusMaintenance ");
            strSql.Append(" WHERE DocID=@DocID");
            SqlParameter[] parameters = SetParametersValue(objKey);
            DbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
        }
        #endregion --SQL--
        #region --Get Data--
        /// <sumary> 
        /// Get Data By Primary Key 
        /// </sumary> 
        /// <param name="objKey">Key of Table</param> 
        /// <returns>DataTable</returns> 
        public DataTable GetDataByKeyValue(params object[] objKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT RecID, DocID, BusID, BusIdentificationNO, MaintenanceTypeID, MaintenanceTypeName, MaintenanceDate, MaintenanceManufacturer,MaintenanceManufacturerDesc,MaintenanceManufacturerOther, MaintenancePrincipal, MaintenanceMajorComponent, TotalCost, MaintenanceOutList, RecDivID, RecAcct, RecDate, Void, VoidAcct, VoidDate, UserAcct, LastUpdateDate");
            strSql.Append(" FROM Bus_BusMaintenance");
            strSql.Append(" WHERE DocID=@DocID");
            SqlParameter[] parameters = SetParametersValue(objKey);
            return DbHelper.ExecuteQuery(strSql.ToString(), parameters).Tables[0];
        }
        /// <sumary> 
        /// Get Data List 
        /// </sumary> 
        /// <param name="strWhere">strWhere Format:AND Colname='?' AND ColName2='?'... </param> 
        /// <returns>DataTable</returns> 
        public DataTable GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            //strSql.Append("SELECT  RecID, DocID, BusID, BusIdentificationNO, MaintenanceTypeID, MaintenanceTypeName, MaintenanceDate, MaintenanceManufacturer, MaintenancePrincipal, MaintenanceMajorComponent, TotalCost, MaintenanceOutList, RecDivID, RecAcct, RecDate, Void, VoidAcct, VoidDate, UserAcct, LastUpdateDate");
            //strSql.Append(" FROM Bus_BusMaintenance ");
            strSql.Append(@"SELECT  Bus_BusMaintenance.RecID, DocID, Bus_BusMaintenance.BusID, 
                                    Bus_BusMaintenance.BusIdentificationNO, MaintenanceTypeID, MaintenanceTypeName, 
                                    MaintenanceDate, MaintenanceManufacturer,MaintenanceManufacturerDesc,MaintenanceManufacturerOther, MaintenancePrincipal, MaintenanceMajorComponent,
                                    TotalCost, MaintenanceOutList, Bus_BusMaintenance.RecDivID, Bus_BusMaintenance.RecAcct, 
                                    Bus_BusMaintenance.RecDate, Bus_BusMaintenance.Void, Bus_BusMaintenance.VoidAcct, 
                                    Bus_BusMaintenance.VoidDate,Bus_BusMaintenance.UserAcct, Bus_BusMaintenance.LastUpdateDate,d.DriverID,d.DriverName
                            FROM Bus_BusMaintenance
                            LEFT JOIN 
		                            (
		                              SELECT d.BusID,d.DriverID,dr.DriverName 
		                              FROM Bus_DriverBuses d 
		                              LEFT JOIN Bus_Drivers dr ON dr.DriverID=d.DriverID AND dr.Void=0

		                            ) d ON d.BusID=Bus_BusMaintenance.BusID");
            strSql.Append(" WHERE Void=0 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }
            return DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        }
        /// <sumary> 
        /// Get TOP Rows Record 
        /// </sumary> 
        /// <param name="top">TOP Rows</param> 
        /// <param name="strWhere">strWhere Format:AND ColName1='?' AND ColName2='?' ..</param> 
        /// <param name="fieldOrder">Field Order By. Format: ColName1,ColName2...</param> 
        /// <returns>DataTable:TOP Rows</returns> 
        public DataTable GetList(int top, string strWhere, string fieldOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT ");
            if (top > 0)
            {
                strSql.Append(" TOP " + top.ToString());
            }
            strSql.Append(" RecID, DocID, BusID, BusIdentificationNO, MaintenanceTypeID, MaintenanceTypeName, MaintenanceDate, MaintenanceManufacturer,MaintenanceManufacturerDesc,MaintenanceManufacturerOther, MaintenancePrincipal, MaintenanceMajorComponent, TotalCost, MaintenanceOutList, RecDivID, RecAcct, RecDate, Void, VoidAcct, VoidDate, UserAcct, LastUpdateDate ");
            strSql.Append(" FROM Bus_BusMaintenance ");
            strSql.Append(" WHERE Void=0 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }
            strSql.Append(" ORDER BY " + fieldOrder);
            return DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        }
        /// <sumary> 
        /// Convert Table to Model List<Model.BusMaintenance> 
        /// </sumary> 
        /// <param name="dt">DataTable Of PMSProjBasic</param> 
        /// <returns>List<Model.BusMaintenance></returns> 
        public List<Model.BusMaintenance> DataTableToList(DataTable dt)
        {
            List<Model.BusMaintenance> modelList = new List<Model.BusMaintenance>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                Model.BusMaintenance model;
                for (int n = 0; n < rowsCount; n++)
                {
                    DataRow dr = dt.Rows[n];
                    model = new Model.BusMaintenance(dr);
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        /// <summary>
        /// Get Data List By Page
        /// </summary>
        public DataTable GetListByPage(int pageSize, int pageIndex, string strWhere)
        {
            SqlParameter[] parameters = {
		   new SqlParameter("@tblName", SqlDbType.VarChar, 255),
		   new SqlParameter("@fldName", SqlDbType.VarChar, 255),
		   new SqlParameter("@PageSize", SqlDbType.Int),
		   new SqlParameter("@PageIndex", SqlDbType.Int),
		   new SqlParameter("@IsReCount", SqlDbType.Bit),
		   new SqlParameter("@OrderType", SqlDbType.Bit),
		   new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
		   };
            parameters[0].Value = "Bus_BusMaintenance";
            parameters[1].Value = "RecID";
            parameters[2].Value = pageSize;
            parameters[3].Value = pageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;
            return DbHelper.ExecuteQueryProcedure("UP_GetRecordByPage", parameters, "ds").Tables[0];
        }

        /// <summary>
        /// 查询数据清单给报表--rptCarApplyList.rpt  计划出车统计表
        /// </summary>
        /// <param name="divID">单位编号</param>
        /// <param name="busIDNO">车辆牌号</param>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <param name="keywords">关键字</param>
        /// <returns>返回DT</returns>
        public DataTable GetListWithDesc(string divID, string busIDNO, string startDate, string endDate, string keywords)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT m.DocID,m.BusID,m.BusIdentificationNO,m.MaintenanceTypeName,
                                b.CompanyID,b.CompanyName,m.MaintenanceDate,m.MaintenanceManufacturer,m.MaintenanceManufacturerDesc,m.MaintenanceManufacturerOther
                                ,m.MaintenancePrincipal,m.MaintenanceMajorComponent,m.TotalCost,m.MaintenanceOutList
                            FROM Bus_BusMaintenance m
                            LEFT JOIN Bus_BusList b ON b.BusID=m.BusID
                          ");
            strSql.Append(" WHERE 1=1 ");
            if (divID != "")
            {
                strSql.Append(" AND b.CompanyID = '" + divID + "'");
            }
            if (busIDNO != "")//页面暂时不需要
            {
                strSql.Append(" AND m.BusIdentificationNO LIKE N'%" + busIDNO + "%'");
            }
            if (startDate != "" && endDate != "")
            {
                strSql.Append(" AND m.MaintenanceDate>='" + startDate + "' ");
                strSql.Append(" AND m.MaintenanceDate<='" + endDate + "' ");
            }
            else if (startDate != "")
            {
                strSql.Append(" AND m.MaintenanceDate<='" + startDate + "' ");
            }
            else if (endDate != "")
            {
                strSql.Append(" AND m.MaintenanceDate<='" + endDate + "' ");
            }
            strSql.Append("   ORDER BY m.MaintenanceDate");
            return DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        }

        #endregion --Get Data--
        #region ----PrivateSet--
        /// <sumary> 
        /// Set Parameters Value 
        /// </sumary> 
        /// <param name="model">Model</param> 
        /// <returns>Parameters</returns> 
        private SqlParameter[] SetParametersValue(Model.BusMaintenance model)
        {
            #region --Init Data--
            if (model.RecDate == DateTime.MinValue)
            {
                model.RecDate = DateTime.Now;
            }
            if (string.IsNullOrEmpty(model.RecAcct))
            {
                model.RecAcct = model.UserAcct;
            }

            model.LastUpdateDate = DateTime.Now;
            #endregion --Init Data--
            SqlParameter[] parameters = { 
                                    		StrHelper.MakeParam("@DocID",SqlDbType.VarChar,20,ParameterDirection.Input,model.DocID),
                                    		StrHelper.MakeParam("@BusID",SqlDbType.VarChar,20,ParameterDirection.Input,model.BusID),
                                    		StrHelper.MakeParam("@BusIdentificationNO",SqlDbType.NVarChar,50,ParameterDirection.Input,model.BusIdentificationNO),
                                    		StrHelper.MakeParam("@MaintenanceTypeID",SqlDbType.VarChar,20,ParameterDirection.Input,model.MaintenanceTypeID),
                                    		StrHelper.MakeParam("@MaintenanceTypeName",SqlDbType.NVarChar,50,ParameterDirection.Input,model.MaintenanceTypeName),
                                    		StrHelper.MakeParam("@MaintenanceDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.MaintenanceDate),
                                    		StrHelper.MakeParam("@MaintenanceManufacturer",SqlDbType.NVarChar,50,ParameterDirection.Input,model.MaintenanceManufacturer),
                                            StrHelper.MakeParam("@MaintenanceManufacturerDesc",SqlDbType.NVarChar,200,ParameterDirection.Input,model.MaintenanceManufacturerDesc),
                                            StrHelper.MakeParam("@MaintenanceManufacturerOther",SqlDbType.NVarChar,200,ParameterDirection.Input,model.MaintenanceManufacturerOther),
                                    		StrHelper.MakeParam("@MaintenancePrincipal",SqlDbType.NVarChar,20,ParameterDirection.Input,model.MaintenancePrincipal),
                                    		StrHelper.MakeParam("@MaintenanceMajorComponent",SqlDbType.NVarChar,4000,ParameterDirection.Input,model.MaintenanceMajorComponent),
                                    		StrHelper.MakeParam("@TotalCost",SqlDbType.Decimal,0,ParameterDirection.Input,model.TotalCost),
                                    		StrHelper.MakeParam("@MaintenanceOutList",SqlDbType.NVarChar,100,ParameterDirection.Input,model.MaintenanceOutList),
                                    		StrHelper.MakeParam("@RecDivID",SqlDbType.VarChar,20,ParameterDirection.Input,model.RecDivID),
                                    		StrHelper.MakeParam("@RecAcct",SqlDbType.VarChar,20,ParameterDirection.Input,model.RecAcct),
                                    		StrHelper.MakeParam("@RecDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.RecDate),
                                            //StrHelper.MakeParam("@Void",SqlDbType.Bit,0,ParameterDirection.Input,model.Void),
                                            //StrHelper.MakeParam("@VoidAcct",SqlDbType.VarChar,20,ParameterDirection.Input,model.VoidAcct),
                                            //StrHelper.MakeParam("@VoidDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.VoidDate),
                                    		StrHelper.MakeParam("@UserAcct",SqlDbType.VarChar,20,ParameterDirection.Input,model.UserAcct),
                                    		StrHelper.MakeParam("@LastUpdateDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.LastUpdateDate),

                                 		};
            return parameters;
        }
        /// <sumary> 
        /// Set Parameter Value of Key 
        /// </sumary> 
        /// <param name="objKey">Key Values</param> 
        /// <returns>Parameters</returns> 
        private SqlParameter[] SetParametersValue(object[] objKey)
        {
            SqlParameter[] parameters = { 
                                    		StrHelper.MakeParam("@DocID",SqlDbType.VarChar,20,ParameterDirection.Input,objKey[0]),

                                 		};
            return parameters;
        }
        #endregion ----PrivateSet--
    }
}
